﻿Option Strict On

Imports Microsoft.VisualBasic
Imports System.Data.OleDb


Public Class NewsDAO
    Dim connStr As String = ConfigurationManager.ConnectionStrings("ungvarberg").ConnectionString

    'Hämta alla nyheter från EN flik
    Public Function GetAllNewsSparetime() As List(Of News)
        Dim list As New List(Of News)

        Dim sql As String = "SELECT tblNews.NewsID, tblNews.NewsSectionID, Header, Texten, DateNews, NewsName FROM tblNews, tblNewsName WHERE tblNews.NewsSectionID = tblNewsName.NewsSectionID AND tblNewsName.NewsSectionID = 1 ORDER BY DateNews DESC"


        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)


        Dim news As New News

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader()

            While reader.Read()
                news = New News

                news.NewsName = reader("NewsName").ToString
                news.Header = reader("Header").ToString
                news.Texten = reader("Texten").ToString
                news.NewsID = CType(reader("NewsID"), Integer)
                news.DateNews = CType(reader("DateNews"), DateTime)
                news.NewsSection = CType(reader("NewsSectionID"), Integer)

                list.Add(news)
            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function

    'Hämta alla nyheter från EN flik
    Public Function GetAllNewsSchoolWork() As List(Of News)
        Dim list As New List(Of News)

        Dim sql As String = "SELECT tblNews.NewsID, tblNews.NewsSectionID, Header, Texten, DateNews, NewsName, Prio, StartPage FROM tblNews, tblNewsName WHERE tblNews.NewsSectionID = tblNewsName.NewsSectionID AND tblNewsName.NewsSectionID = 2 ORDER BY DateNews DESC"


        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)


        Dim news As New News

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader()

            While reader.Read()
                news = New News

                news.NewsName = reader("NewsName").ToString
                news.Header = reader("Header").ToString
                news.Texten = reader("Texten").ToString
                news.NewsID = CType(reader("NewsID"), Integer)
                news.DateNews = CType(reader("DateNews"), DateTime)
                news.NewsSection = CType(reader("NewsSectionID"), Integer)
                news.Prio = CType(reader("Prio"), Boolean)
                news.StartPage = CType(reader("StartPage"), Boolean)

                list.Add(news)
            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function

    'Hämtar TOP8 nyheter ifrån alla kategorier
    Public Function getAllNews() As List(Of News)
        Dim list As New List(Of News)
        Dim sql As String = "SELECT TOP 8 tblNews.NewsID, tblNews.NewsSectionID, Header, Texten, DateNews, NewsName, StartPage,Prio FROM tblNews, tblNewsName WHERE tblNews.NewsSectionID = tblNewsName.NewsSectionID  ORDER BY Prio, DateNews DESC"

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        Dim news As New News

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader

            While reader.Read
                news = New News

                news.NewsName = reader("NewsName").ToString
                news.Header = reader("Header").ToString
                news.Texten = reader("Texten").ToString
                news.NewsID = CType(reader("NewsID"), Integer)
                news.DateNews = CType(reader("DateNews"), DateTime)
                news.NewsSection = CType(reader("NewsSectionID"), Integer)
                news.StartPage = CBool(reader("StartPage"))
                news.Prio = CBool(reader("Prio"))

                list.Add(news)

            End While

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return list
    End Function

    'Hämta VALD nyhet
    Public Function getOneNews(ByVal id As Integer) As News
        Dim sql As String = "SELECT NewsID, Header, Texten, NewsName, DateNews, Prio,StartPage,tblNews.NewsSectionID FROM tblNews, tblNewsName WHERE tblNews.NewsSectionID = tblNewsName.NewsSectionID AND NewsID = @id"

        Dim connection As New OleDbConnection(connStr)
        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("@id", id)

        Dim news As New News

        Try
            connection.Open()
            Dim reader As OleDbDataReader = command.ExecuteReader

            If reader.Read() Then
                news = New News

                news.NewsID = CType(reader("NewsID"), Integer)
                news.Header = reader("Header").ToString
                news.NewsName = reader("newsName").ToString
                news.Texten = reader("Texten").ToString
                news.DateNews = CType(reader("DateNews"), Date)
                news.StartPage = CType(reader("StartPage"), Boolean)
                news.Prio = CType(reader("Prio"), Boolean)
                news.NewsSectionID = CType(reader("NewsSectionID"), Integer)
            End If
        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

        Return news
    End Function

    'lägger in ny nyhet
    Public Sub createNewNews(ByVal news As News)
        Dim sql As String = "INSERT INTO tblNews(Texten, NewsSectionID, Startpage,Prio)" & _
        " values(@Texten,@NewsSectionID,@StartPage,@Prio);"

        Dim connection As New OleDbConnection(connStr)

        Dim command As New OleDbCommand(sql, connection)

        'command.Parameters.AddWithValue("@Header", news.Header)
        command.Parameters.AddWithValue("@Texten", news.Texten)
        command.Parameters.AddWithValue("@NewsSectionID", news.NewsSectionID)
        command.Parameters.AddWithValue("@StartPage", news.StartPage)
        command.Parameters.AddWithValue("@Prio", news.Prio)
        ' command.Parameters.AddWithValue("@Filename", news.Filename)
        '  command.Parameters.AddWithValue("@datum", news.datum)

        Try
            connection.Open()
            command.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

    End Sub
    'editerar en nyhet
    Public Sub EditNews(ByVal news As News)
        Dim sql As String = "UPDATE tblNews SET prio = @prio ,startpage = @startpage WHERE newsID = @newsID;"

        Dim connection As New OleDbConnection(connStr)

        Dim command As New OleDbCommand(sql, connection)




        command.Parameters.AddWithValue("@prio", news.Prio)
        command.Parameters.AddWithValue("@startpage", news.StartPage)
        command.Parameters.AddWithValue("@newsID", news.NewsID)


        Try
            connection.Open()
            command.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try
    End Sub
    'tar bort nyhet
    Public Sub deleteNewsByID(ByVal id As Integer)
        Dim sql As String = "DELETE * FROM tblnews WHERE newsID = @ID;"

        Dim connection As New OleDbConnection(connStr)

        Dim command As New OleDbCommand(sql, connection)

        command.Parameters.AddWithValue("@ID", id)


        Try
            connection.Open()
            command.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            connection.Close()
            connection.Dispose()
        End Try

    End Sub

End Class
